package edu.beyond.employment.company.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import edu.beyond.employment.commom.PageModel;
import edu.beyond.employment.commom.util.DBUtil;
import edu.beyond.employment.commom.util.Util;
import edu.beyond.employment.company.entity.ApplicationEntity;

/**
 * 申请职位信息Dao
 * 
 * @author GOBeyond
 * @date 2016年10月7日
 */
public class ApplicationDao {
	private static final Logger LOG = Logger.getLogger(ApplicationDao.class);

	/**
	 * 分页显示
	 */
	public PageModel<ApplicationEntity> listTalentApplyByPage(int talentId, int pageNo, int pageSize) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("p.name as 'position$name', ");
		sb.append("c.name as 'company$name', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");
		sb.append("ORDER BY id DESC ");
		sb.append("LIMIT ?, ? ");

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, talentId);
			// 从哪条记录开始
			preparedStatement.setInt(2, (pageNo - 1) * pageSize);
			// 每页显示多少条记录
			preparedStatement.setInt(3, pageSize);
			resultSet = preparedStatement.executeQuery();
			// 每批数据
			List<ApplicationEntity> applicationList = new ArrayList<>();
			while (resultSet.next()) {
				// 第一条数据
				ApplicationEntity application = new ApplicationEntity();
				application.setResume$intention(resultSet.getString("resume$intention"));
				application.setPosition$name(resultSet.getString("position$name"));
				application.setCompany$name(resultSet.getString("company$name"));
				application.setId(resultSet.getInt("id"));
				application.setAppTime(resultSet.getTimestamp("app_time"));
				application.setStatus(resultSet.getInt("status"));
				application.setHandleTime(resultSet.getTimestamp("handle_time"));
				// 把每一条数据放到一批数据里
				applicationList.add(application);
			}
			// 构建分页实体
			return new PageModel<ApplicationEntity>(applicationList, pageNo, pageSize,
					getAllTalentApplyRecords(talentId));
		} catch (SQLException e) {
			Util.e(LOG, "分页查询某个求职者申请信息失败!", e);
			throw new RuntimeException();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 首页显示某个求职者的求职信息
	 * 
	 * @param id
	 * @return
	 */

	public List<ApplicationEntity> list(int id) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("r.intention AS 'resume$intention',");
		sb.append("p.`name`AS' position$name',");
		sb.append("c.`name`AS'company$name',");
		sb.append("a.id,");
		sb.append("a.app_time,");
		sb.append("a.`status` ");
		sb.append("FROM application a,resume r, position p,company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id=? ");
		List<ApplicationEntity> applicationList = DBUtil.executeQuery(ApplicationEntity.class, sb.toString(), id);
		return applicationList;
	}

	/**
	 * 删除某个申请信息
	 * 
	 * @return
	 */
	public int delete(int id) {
		String sql = "DELETE FROM application WHERE id = ?";
		return DBUtil.executeUpdate(sql, id);
	}

	/**
	 * 求职者的简历投递
	 * 
	 * @param talentId
	 * @param resumeId
	 * @param positionId
	 * @return
	 */
	public int add(int talentId, int resumeId, int positionId) {
		String sql = "INSERT INTO application VALUES(NULL, ?, ?, ?, 1, NOW(), NULL);";
		return DBUtil.executeUpdate(sql, positionId, talentId, resumeId);
	}

	/**
	 * 查询某个求职者申请信息的总记录数
	 * 
	 * @param talentId
	 * @return
	 */
	private static int getAllTalentApplyRecords(int talentId) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("COUNT(*) ");
		sb.append("FROM application a, resume r, position p, company c ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND p.company_id = c.id ");
		sb.append("AND a.talent_id = ? ");

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			preparedStatement.setInt(1, talentId);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (SQLException e) {
			Util.e(LOG, "分页时，查询某个求职者申请信息的总记录数失败!", e);
			throw new RuntimeException();
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 是否申请该职位
	 * 
	 * @param talentId
	 * @param id
	 * @return
	 */
	public ApplicationEntity isApplied(int talentId, int id) {
		String sql = "SELECT * FROM application WHERE talent_id=? AND position_id=?";
		return DBUtil.getUniqueResult(ApplicationEntity.class, sql, talentId, id);
	}

	public PageModel<ApplicationEntity> listCompanyVerifyByPage(ApplicationEntity application, int pageNo,
			int defaultPageSize) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * 企业审核求职者的申请
	 * 
	 * @param status
	 * @param applicationId
	 * @return
	 */
	public int verify(int status, int applicationId) {
		String sql = "UPDATE application SET status=?, handle_time=NOW() WHERE id=?";
		return DBUtil.executeUpdate(sql, status, applicationId);
	}

}
